package com.jy.appmarket.database;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.locks.ReentrantLock;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.util.Log;

import com.jy.appmarket.model.AppUpdateModel;
import com.jy.appmarket.utils.PackageUtils;

public final class AppUpdateDatabase {
	private static final String TAG = "AppUpdateDatabase";

	public static final Uri URI = Uri.parse("sqlite://com.jy.appmarket/appupdate");

	private static final String DB_NAME = "appupdate";
	private static final String DB_TABLE = "updates";
	private static final int DB_VERSION = 1;

	public static final String COL_ID = "_id";// long
	public static final String COL_LAST_MODIFICATION = "last_modification";// long

	public static final String COL_NAME = "name";// String
	public static final String COL_DL_URL = "dl_url";// String
	public static final String COL_ICON_URL = "icon_url";// String
	public static final String COL_PKG_NAME = "pkg_name";// String
	public static final String COL_VERSION_CODE = "version_code";// int
	public static final String COL_VERSION_NAME = "version_name";// String
	public static final String COL_MD5 = "md5";// String
	public static final String COL_CHANGELOG = "changelog";// String
	public static final String COL_FILESIZE = "filesize";// long
	public static final String COL_LASTMODIFIED = "last_modified";// long

	public static final String COL_IGNORE = "is_ignore";// int

	private static final String CREATE_SQL = "CREATE TABLE " + DB_TABLE + " (" + COL_ID
			+ " BIGINT PRIMARY KEY," + COL_LAST_MODIFICATION + " BIGINT," + COL_NAME + " TEXT,"
			+ COL_DL_URL + " TEXT," + COL_ICON_URL + " TEXT," + COL_PKG_NAME + " TEXT UNIQUE,"
			+ COL_VERSION_CODE + " INTEGER," + COL_VERSION_NAME + " TEXT," + COL_MD5 + " TEXT,"
			+ COL_CHANGELOG + " TEXT," + COL_FILESIZE + " BIGINT," + COL_LASTMODIFIED + " BIGINT,"
			+ COL_IGNORE + " INTEGER);";

	private static class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(CREATE_SQL);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		}
	}

	private static AppUpdateDatabase sInstance = null;
	private DatabaseHelper mHelper = null;
	private Context mContext;
	private ReentrantLock mLock;

	private AppUpdateDatabase(Context context) {
		mLock = new ReentrantLock();
		mContext = context.getApplicationContext();
		mHelper = new DatabaseHelper(mContext);
	}

	public static AppUpdateDatabase getInstance(Context context) {
		if (sInstance == null) {
			synchronized (AppUpdateDatabase.class) {
				if (sInstance == null) {
					sInstance = new AppUpdateDatabase(context);
				}
			}
		}
		return sInstance;
	}

	/**
	 * 修正数据库中的数据：删除存在于表中但是并没有安装的app条目，或者安装了但是安装版本号与数据库中版本号相同或更高的条目
	 * */
	public void correctAppUpdatesDatas(Context context) {
		final ReentrantLock lock = mLock;
		lock.lock();
		try {

			if (context == null) {
				return;
			}
			boolean changed = false;
			SQLiteDatabase db = mHelper.getWritableDatabase();
			List<AppUpdateModel> all = getAllUpdatesOrderBy(null, false, true);
			for (AppUpdateModel update : all) {
				Integer vc = PackageUtils.getVersionCode(context, update.getPackageName());
				if (vc == null || vc >= update.getVersionCode()) {
					int ret = db.delete(DB_TABLE, COL_PKG_NAME + "=?",
							new String[] { update.getPackageName() });
					if (ret > 0) {
						changed = true;
					}
				}
			}
			if (changed) {
				mContext.getContentResolver().notifyChange(URI, null);
			}
		} finally {
			lock.unlock();
		}
	}

	/**
	 * 获取表中条目总数
	 * 
	 * @param includeIgnored
	 *            :是否包含忽略的app
	 * @return
	 */
	public int getCount(boolean includeIgnored) {
		final ReentrantLock lock = mLock;
		lock.lock();
		try {
			int count = 0;
			Cursor cur = null;
			try {
				SQLiteDatabase db = mHelper.getReadableDatabase();
				String selection = includeIgnored ? null : COL_IGNORE + "=?";
				String[] selectionArgs = includeIgnored ? null : new String[] { "0" };

				cur = db.query(DB_TABLE, new String[] { "COUNT(*)" }, selection, selectionArgs,
						null, null, null);
				if (cur.moveToFirst()) {
					count = cur.getInt(0);
				}
				return count;
			} finally {
				if (cur != null) {
					cur.close();
				}
			}
		} finally {
			lock.unlock();
		}
	}

	public List<AppUpdateModel> getAllUpdatesOrderBy(String colName, boolean isDesc,
			boolean includeIgnored) {
		final ReentrantLock lock = mLock;
		lock.lock();
		try {
			String orderBy = null;
			if (COL_LASTMODIFIED.equals(colName) || COL_LAST_MODIFICATION.equals(colName)
					|| COL_NAME.equals(colName) || COL_FILESIZE.equals(colName)) {
				orderBy = colName + (isDesc ? " DESC" : "");
			}
			Cursor cur = null;
			try {
				SQLiteDatabase db = mHelper.getReadableDatabase();
				List<AppUpdateModel> updates = new ArrayList<AppUpdateModel>();
				String selection = includeIgnored ? null : COL_IGNORE + "=?";
				String[] selectionArgs = includeIgnored ? null : new String[] { "0" };
				cur = db.query(DB_TABLE, null, selection, selectionArgs, null, null, orderBy);
				for (cur.moveToFirst(); !cur.isAfterLast(); cur.moveToNext()) {
					AppUpdateModel update = buildFromCursor(cur);
					if (update != null) {
						updates.add(update);
					}
				}
				return updates;
			} finally {
				if (cur != null) {
					cur.close();
				}
			}
		} finally {
			lock.unlock();
		}
	}

	/**
	 * 设置忽略
	 * 
	 * @param pkgName
	 *            :包名
	 * @param ignore
	 *            :是否忽略
	 * @return 数据库是否发生改变
	 */
	public boolean setIgnore(String pkgName, boolean ignore) {
		final ReentrantLock lock = mLock;
		lock.lock();
		try {
			if (pkgName == null) {
				return false;
			}
			AppUpdateModel update = get(pkgName);
			if (update == null) {
				return false;
			}
			if (ignore == update.isIgnore()) {
				return false;
			}

			ContentValues cv = toContentValues(update);
			cv.put(COL_IGNORE, ignore ? 1 : 0);
			SQLiteDatabase db = mHelper.getWritableDatabase();
			int status = db.update(DB_TABLE, cv, COL_PKG_NAME + "=?", new String[] { pkgName });
			mContext.getContentResolver().notifyChange(URI, null);
			return status == 1;
		} finally {
			lock.unlock();
		}
	}

	public AppUpdateModel get(String pkgName) {
		final ReentrantLock lock = mLock;
		lock.lock();
		try {
			if (pkgName == null) {
				return null;
			}

			SQLiteDatabase db = mHelper.getReadableDatabase();
			Cursor cur = null;
			try {
				cur = db.query(DB_TABLE, null, COL_PKG_NAME + "=?", new String[] { pkgName }, null,
						null, null);
				if (cur.moveToFirst()) {
					return buildFromCursor(cur);
				}
			} finally {
				if (cur != null) {
					cur.close();
				}
			}
			return null;
		} finally {
			lock.unlock();
		}
	}

	public int clear() {
		final ReentrantLock lock = mLock;
		lock.lock();
		try {
			SQLiteDatabase db = mHelper.getWritableDatabase();
			mContext.getContentResolver().notifyChange(URI, null);
			return db.delete(DB_TABLE, null, null);
		} finally {
			lock.unlock();
		}
	}


	public long insert(AppUpdateModel update) {
		final ReentrantLock lock = mLock;
		lock.lock();
		try {
			SQLiteDatabase db = mHelper.getWritableDatabase();
			ContentValues cv = toContentValues(update);
			long now = System.currentTimeMillis();
			cv.put(COL_LAST_MODIFICATION, now);
			mContext.getContentResolver().notifyChange(URI, null);
			return db.insert(DB_TABLE, null, cv);
		} finally {
			lock.unlock();
		}
	}

	public static AppUpdateModel buildFromCursor(Cursor cur) {
		if (cur == null) {
			Log.w(TAG, "cursor is null");
			return null;
		}
		AppUpdateModel di = new AppUpdateModel();
		di.set_id(cur.getLong(cur.getColumnIndex(COL_ID)));
		di.setLastModification(cur.getLong(cur.getColumnIndex(COL_LAST_MODIFICATION)));
		di.setTitle(cur.getString(cur.getColumnIndex(COL_NAME)));
		di.setDownloadUrl(cur.getString(cur.getColumnIndex(COL_DL_URL)));
		di.setIconPath(cur.getString(cur.getColumnIndex(COL_ICON_URL)));
		di.setPackageName(cur.getString(cur.getColumnIndex(COL_PKG_NAME)));
		di.setVersionCode(cur.getInt(cur.getColumnIndex(COL_VERSION_CODE)));
		di.setVersionName(cur.getString(cur.getColumnIndex(COL_VERSION_NAME)));
		di.setFileMd5(cur.getString(cur.getColumnIndex(COL_MD5)));
		di.setChangeLog(cur.getString(cur.getColumnIndex(COL_CHANGELOG)));
		di.setSize(cur.getLong(cur.getColumnIndex(COL_FILESIZE)));
		di.setLastModified(cur.getLong(cur.getColumnIndex(COL_LASTMODIFIED)));
		di.setIgnore(cur.getInt(cur.getColumnIndex(COL_IGNORE)) == 1);
		return di;
	}

	public static ContentValues toContentValues(AppUpdateModel update) {
		if (update == null) {
			Log.w(TAG, "cursor is null");
			return null;
		}
		ContentValues cv = new ContentValues();
		cv.put(COL_NAME, update.getTitle());
		cv.put(COL_DL_URL, update.getDownloadUrl());
		cv.put(COL_ICON_URL, update.getIconPath());
		cv.put(COL_PKG_NAME, update.getPackageName());
		cv.put(COL_VERSION_CODE, update.getVersionCode());
		cv.put(COL_VERSION_NAME, update.getVersionName());
		cv.put(COL_MD5, update.getFileMd5());
		cv.put(COL_CHANGELOG, update.getChangeLog());
		cv.put(COL_FILESIZE, update.getSize());
		cv.put(COL_LASTMODIFIED, update.getLastModified());
		cv.put(COL_IGNORE, update.isIgnore() ? 1 : 0);
		return cv;

	}
}
